package com.pokermind.dse.config;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.Statement;

/**
 * 数据库配置类 配置JdbcTemplate、事务管理器等 (MySQL)
 *
 * @author PokerMind Team
 */
@Configuration
@EnableTransactionManagement
public class DatabaseConfig {

    private static final org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(DatabaseConfig.class);

    @Value("${pokermind.database.init-on-startup:true}")
    private boolean initOnStartup;

    @Value("${pokermind.database.execute-schema:true}")
    private boolean executeSchema;

    /**
     * JdbcTemplate Bean配置
     */
    @Bean
    @Primary
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        // 设置查询超时时间（秒）
        jdbcTemplate.setQueryTimeout(30);

        // 设置最大行数限制
        jdbcTemplate.setMaxRows(10000);

        // 设置获取大小（影响批量操作性能）
        jdbcTemplate.setFetchSize(100);

        // 如果需要在启动时初始化数据库
        //        if (initOnStartup) {
        //            initializeDatabase(dataSource);
        //
        //            // 执行完整Schema修复
        //            try {
        //                com.pokermind.dse.util.CompleteSchemaFixer schemaFixer =
        //                    new com.pokermind.dse.util.CompleteSchemaFixer(jdbcTemplate);
        //                schemaFixer.fixCompleteSchema();
        //                schemaFixer.showTableStructure();
        //            } catch (Exception e) {
        //                logger.warn("Complete schema fix failed, but continuing: {}", e.getMessage());
        //            }
        //        }

        return jdbcTemplate;
    }

    /**
     * 事务管理器配置
     */
    @Bean
    @Primary
    public PlatformTransactionManager transactionManager(DataSource dataSource) {
        DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(dataSource);

        // 设置事务超时时间（秒）
        transactionManager.setDefaultTimeout(60);

        // 设置是否在开始事务时验证现有事务
        transactionManager.setValidateExistingTransaction(true);

        return transactionManager;
    }

    /**
     * 初始化数据库连接 (MySQL)
     */
    private void initializeDatabase(DataSource dataSource) {
        try (Connection connection = dataSource.getConnection(); Statement statement = connection.createStatement()) {

            // MySQL 连接参数设置
            statement.execute(
                "SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO'");
            statement.execute("SET SESSION time_zone = '+08:00'");
            statement.execute("SET SESSION autocommit = 1");

            // 验证数据库连接和表存在性
            if (tableExists(connection, "poker_users")) {
                System.out.println("MySQL Database connection established successfully - Tables verified");
            } else {
                System.out.println("MySQL Database connected but tables not found - Please ensure tables are created");
            }

        } catch (Exception e) {
            System.err.println("Failed to connect to MySQL database: " + e.getMessage());
            throw new RuntimeException("MySQL Database connection failed", e);
        }
    }

    /**
     * 检查表是否存在 (MySQL)
     */
    private boolean tableExists(Connection connection, String tableName) {
        try (Statement statement = connection.createStatement()) {
            // MySQL: 使用 INFORMATION_SCHEMA 查询表是否存在
            var resultSet = statement.executeQuery(
                "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES " + "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '" + tableName + "'");
            return resultSet.next();
        } catch (Exception e) {
            System.err.println("Error checking table existence: " + e.getMessage());
            return false;
        }
    }
}